# -*- coding: utf-8 -*-
"""
Created on Tue Aug 23 16:53:04 2022

@author: admin
"""

import pandas as pd
from sqlalchemy import create_engine

from sqlalchemy.ext.automap import automap_base
from sqlalchemy import MetaData,inspect
from sqlalchemy.orm import sessionmaker 


class DataBase():
    '''数据库基本操作
    
    # 使用案例
    yunwind = YunWind() 
    sql = 'select S_INFO_WINDCODE,S_INFO_COMPNAME from AINDEXDESCRIPTION limit 100'
    data = yunwind.read_sql(sql)
    
    '''
    def read_sql(self, sql):
        

        engine = create_engine(self.uri)

        return pd.read_sql(sql,engine)
    
    def append(self, df, table,fast_executemany = False):
        
        if fast_executemany:
            engine = create_engine(self.uri,fast_executemany=True)
        else:
            engine = create_engine(self.uri)   
            
        pd.io.sql.to_sql( df , table , engine,index = False,
                         chunksize = None , if_exists = 'append'
                         ) 
    
    def replace( self, df, table  ,dtypes=None,fast_executemany=False):
        
        if fast_executemany:
            engine = create_engine(self.uri,fast_executemany=True)
        else:
            engine = create_engine(self.uri)   

        return pd.io.sql.to_sql(\
                                df,table,engine,index = False,
                                chunksize = None,if_exists = 'replace',
                                dtype = dtypes
                                ) 
    
    def drop(self,drop_sql):
        
        engine = create_engine(self.uri) 
        session = sessionmaker(engine) 
        Session = session()
        
        Session.execute(drop_sql)
        Session.commit()
        Session.close()
        
        
    def get_table_field_info(self,table_name):
        '''
        获取表各个字段的信息

        Parameters
        ----------
        table_name : str
            表名.

        Returns
        -------
        res : dataframe
        
                字段名  是否为主键      字段类型 是    否允许非空      注释
        0        id      1.0           VARCHAR(100)    0.0        对象ID
        1  windcode    False          VARCHAR(40)     True        Wind代码
        2    stcode    False          VARCHAR(40)     True        交易代码
        
        例子：
        windyun = WindYun() 
        info = windyun.get_table_field_info('asharedescription') 
        
        '''
        
        engine = create_engine(self.uri)

        # session = sessionmaker(bind=engine)()
        
        # 将数据库的表反射出来
        metadata = MetaData(bind=engine)
        
        metadata.reflect(bind=engine, schema=self.db , only=[ table_name ])
        
        Base = automap_base(metadata=metadata)
        
        Base.prepare()
        
        # ow就是table_name表对应的类
        ow = getattr(Base.classes, table_name )
        columns = inspect(ow).columns 
        
        res = pd.DataFrame()
        for col_attr in columns:

             res = res.append({'字段名':col_attr.name,'是否为主键':col_attr.primary_key,
                               '字段类型':str(col_attr.type),'是否允许非空':col_attr.nullable ,
                               '注释':col_attr.comment},ignore_index=True) 
        return res 
        
    
class YunWind(DataBase):
    '''云盘的 Wind数据库'''
    def __init__(self):
        self.uri = 'mysql+pymysql://student2:gtja20@124.223.31.193:3306/wind'     
        self.db = 'wind' 

        
class YunMacro(DataBase):
    '''云盘的Macro数据库'''
    def __init__(self):
        self.uri = 'mysql+pymysql://student2:gtja20@124.223.31.193:3306/macro'     
        self.db = 'macro' 
        


class gtjadisplay(DataBase):
    def __init__(self):
        pass 
    
class Local_sector_indicator(DataBase):
    def __init__(self):
        pass 
        
class Project_lyy(DataBase):
    def __init__(self):
        pass 

class Factor_data(DataBase):
    def __init__(self):
        pass 
        
class FactordbYun(DataBase):
    def __init__(self):
        pass 
        
class BarraYun(DataBase):
    def __init__(self):
        pass 
        
class WindCompany(DataBase):
    def __init__(self):
        pass 
        
